﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL
{
    class LoanEntity
    {
        public Int32 Id { get; set; }
        public Int32 Value { get; set; }
        public Int32 Period { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime PayDate { get; set; }
        public DateTime EndDate { get; set; }
        public Int32 Instalment_amount { get; set; }
        public String Instalment_every { get; set; }
        public Int32 Customer_Id { get; set; }
        public Int32 Project_ID { get; set; }
        public Int32 Rest { get; set; }
        public Int32 Penalties { get; set; }


    }

    /*
 
        Class Client which contains all methods related to Customer table
 
    */


    class Loan
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string conString = ConfigurationManager.ConnectionStrings["ChairtyBank"].ToString();
        private static string cmdText = "";




        /*get Loan specified to specific customer */
        public static LoanEntity GetLoan(Int32 customer_id)
        {
            LoanEntity l = null;


            cmdText = "select * from Loan where Customer_Serial=@Customer_Serial";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Customer_Serial", customer_id);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    l = fillEntityFromReader(reader);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if(con.State == ConnectionState.Open)
                    con.Close();
            }

            return l;
        }
      
        
        public static LoanEntity GetLoanById(Int32 loan_ID)
        {
            LoanEntity l = null;


            cmdText = "select * from Loan where Loan_ID=@Customer_Serial";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Customer_Serial", loan_ID);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    l = fillEntityFromReader(reader);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return l;
        }
        
        
        
        public static LoanEntity GetLoan(Int32 customer_id, Int32 projectId)
        {
            LoanEntity l = null;


            cmdText = "select * from Loan where Customer_Serial=@Customer_Serial And Project_ID=@Project_ID";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Customer_Serial", customer_id);
            cmd.Parameters.AddWithValue("@Project_ID", projectId);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    l = fillEntityFromReader(reader);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return l;
        }
               
     

        public static List<LoanEntity> GetAllLoans()
        {
            LoanEntity l = null;
            List<LoanEntity> loans = new List<LoanEntity>();

            cmdText = "select * from Loan ";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    l = fillEntityFromReader(reader);
                    loans.Add(l);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
                //MessageBox.Show("number of rows=" + loans.Count);
            }

            return loans;

        }


        public static List<LoanEntity> GetLoansByProjectID(Int32 pId)
        {
            LoanEntity l = null;
            List<LoanEntity> loans = new List<LoanEntity>();

            cmdText = "select * from Loan Where Project_ID=@Project_ID";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Project_ID", pId);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    l = fillEntityFromReader(reader);
                    loans.Add(l);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
                //MessageBox.Show("number of rows=" + loans.Count);
            }

            return loans;

        }
        

        public static bool Add(LoanEntity l)
        {
            bool flag = true;
            cmdText = "insert into Loan (Loan_value,Loan_period,Loan_StartDate,Loan_EndDate,Instalment_Amount,Instalment_every,Customer_serial,Project_ID,Loan_Rest,Loan_PayDate,Penalties )"+
                "values(@Loan_value,@Loan_period,@Loan_StartDate,@Loan_EndDate,@Loan_NumberOf,@Instalment_every,@Customer_serial,@Project_ID,@Loan_Rest,@Loan_PayDate,@Penalties)";
              
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Loan_value", l.Value);
            cmd.Parameters.AddWithValue("@Loan_period", l.Period);
            cmd.Parameters.AddWithValue("@Loan_StartDate", l.StartDate);
            cmd.Parameters.AddWithValue("@Loan_EndDate", l.EndDate);
            cmd.Parameters.AddWithValue("@Loan_NumberOf", l.Instalment_amount);
            cmd.Parameters.AddWithValue("@Instalment_every", l.Instalment_every);
            cmd.Parameters.AddWithValue("@Customer_serial", l.Customer_Id);
            cmd.Parameters.AddWithValue("@Project_ID",l.Project_ID);
            cmd.Parameters.AddWithValue("@Loan_Rest", l.Rest);
            cmd.Parameters.AddWithValue("@Loan_PayDate", l.PayDate);
            cmd.Parameters.AddWithValue("@Penalties", 0);
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
              
                
            }
            catch(Exception ex){
                MessageBox.Show("Exception==>>"+ex.Message);
                flag = false;
            }
            finally
            {
                if(con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;

        }
        


        public static bool Update(LoanEntity l)
        {
            Boolean flag = false;
            cmdText = "UPDATE Loan SET Loan_value=@Loan_value ,Loan_period=@Loan_period ,Loan_StartDate=@Loan_StartDate ,Loan_EndDate=@Loan_EndDate"+
                ",Instalment_Amount=@Loan_NumberOf, Instalment_every=@Instalment_every, Customer_serial=@Customer_serial ,Project_ID=@Project_ID ,Loan_Rest=@Loan_Rest, Loan_PayDate=@Loan_PayDate WHERE Loan_ID=@Loan_ID";
            
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
     
            cmd.Parameters.AddWithValue("@Loan_value", l.Value);
            cmd.Parameters.AddWithValue("@Loan_period", l.Period);
            cmd.Parameters.AddWithValue("@Loan_StartDate", l.StartDate);
            cmd.Parameters.AddWithValue("@Loan_EndDate", l.EndDate);
            cmd.Parameters.AddWithValue("@Loan_NumberOf", l.Instalment_amount);
            cmd.Parameters.AddWithValue("@Instalment_every", l.Instalment_every);
            cmd.Parameters.AddWithValue("@Customer_serial", l.Customer_Id);
            cmd.Parameters.AddWithValue("@Project_ID", l.Project_ID);
            cmd.Parameters.AddWithValue("@Loan_Rest", l.Rest);
            cmd.Parameters.AddWithValue("@Loan_PayDate", l.PayDate);
        //    cmd.Parameters.AddWithValue("@Penalties", l.Penalties);
            cmd.Parameters.AddWithValue("@Loan_ID", l.Id);
            
        
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
              //  MessageBox.Show(numOfRow + "Record updated");
                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }


        public static bool UpdateProjectId(LoanEntity l)
        {
            Boolean flag = false;
            cmdText = "UPDATE Loan SET Project_ID=@Project_ID WHERE Loan_ID=@Loan_ID";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            cmd.Parameters.AddWithValue("@Project_ID", l.Project_ID);
            cmd.Parameters.AddWithValue("@Loan_ID", l.Id);


            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                if (numOfRow >0)
                    flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }

        public static bool Update(InstalmentEntity IE)
        {
            LoanEntity l = Loan.GetLoanById(IE.Loan_ID);
            Int32 totPen = l.Penalties + IE.Penalty;
            Boolean flag = false;
            cmdText = "UPDATE Loan SET Loan_Rest=@LoanReset ,Penalties=@Penalties WHERE Loan_ID=@Loan_ID";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            
            cmd.Parameters.AddWithValue("@LoanReset",IE.Rest);
            cmd.Parameters.AddWithValue("@Penalties", totPen);
            cmd.Parameters.AddWithValue("@Loan_ID", IE.Loan_ID);


            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                //MessageBox.Show(numOfRow + "Record updated");
                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }
        public static bool DeletePayed(int loanId,int Penality,int Payed)
        {
            LoanEntity l = Loan.GetLoanById(loanId);
            Int32 totPen = l.Penalties -Penality;
            Int32 loanReset = l.Rest + Payed;
            Boolean flag = false;
            cmdText = "UPDATE Loan SET Loan_Rest=@LoanReset ,Penalties=@Penalties WHERE Loan_ID=@Loan_ID";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            cmd.Parameters.AddWithValue("@LoanReset", loanReset);
            cmd.Parameters.AddWithValue("@Penalties", totPen);
            cmd.Parameters.AddWithValue("@Loan_ID", loanId);


            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                //MessageBox.Show(numOfRow + "Record updated");
                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }

             /*
                Delete using id or Serial 
             */

        public static bool Delete(LoanEntity l)
        {
            Boolean flag = false;
            cmdText = "Delete from Loan WHERE Loan_ID=@Loan_ID";

           
                SqlConnection con = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand(cmdText, con);
                cmd.Parameters.AddWithValue("@Loan_ID", l.Id);
                try
                {
                    con.Open();

                    Int32 numOfRow = cmd.ExecuteNonQuery();
              //      MessageBox.Show(numOfRow + "Record Deleted");
                    flag = true;


                }
                catch (Exception ex)
                {
                    MessageBox.Show("Exception==>>" + ex.Message);
                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();
                
            }
            return flag;
        }







        private static LoanEntity fillEntityFromReader(SqlDataReader reader)
        {
            LoanEntity l = new LoanEntity();
          
            l.Id = Int32.Parse(reader["Loan_ID"].ToString());
            //
            l.Customer_Id = Int32.Parse(reader["Customer_serial"].ToString());
            l.Project_ID = Int32.Parse(reader["Project_ID"].ToString());

            if (reader["Loan_value"] != null)
                l.Value = Int32.Parse(reader["Loan_value"].ToString());
            if (reader["Loan_period"] != null)
                l.Period = Int32.Parse(reader["Loan_period"].ToString());
           if (reader["Loan_StartDate"] != null)
                l.StartDate = (DateTime)reader["Loan_StartDate"];
           if (reader["Loan_PayDate"] != null)
               l.PayDate = (DateTime)reader["Loan_PayDate"];
           if (reader["Loan_EndDate"] != null)
               l.EndDate = (DateTime)reader["Loan_EndDate"];
           if (reader["Instalment_Amount"] != null)
                l.Instalment_amount = Int32.Parse(reader["Instalment_Amount"].ToString());
            if (reader["Instalment_every"] != null)
                l.Instalment_every = reader["Instalment_every"].ToString();
            if (reader["Loan_Rest"] != null)
                l.Rest = Int32.Parse(reader["Loan_Rest"].ToString());
            if (reader["Penalties"] != null)
                l.Penalties = Int32.Parse(reader["Penalties"].ToString());

           
            return l;
        }

         
    }
}
